Copy Values To

Use the Copy Values To tool to copy up a particular field value to the Primary table to create columns like Most recent transaction on the customer table or Highest Earner on the household table.

About this task

In relational databases, there is a fundamental problem when there is a requirement to copy up data from a foreign table to a primary table i.e. from the MANY to the ONE table. The reason for this is the difficulty in deciding which of the MANY records to copy. For example, if you wanted to copy Transaction.Date from the Order table up to the Customer table, how would you select which Transaction Date to use if there is more than one transaction for a customer?

This scenario is addressed in Campaign Manager with the ability to differentiate records in the MANY table using the Rank by X functionality which assigns linked records a rank according to the relative value of another field in the table, like a transaction date or a numeric value.

The following example uses a rank of customer profitability to create a column on the household table of most profitable customer in the house. If you make a column permanent then it will be visible and available in the Data Explorer just like any other column.

Procedure

1. Drag the Copy Values To tool to the Workspace.
2. Choose the join operator.
3. Click the icon to the right of the New Copy Values To field.
4. In the Display Name field, enter a display name for the column.

If you save the tool as a template, this name will be used to display it in the Templates tab. The Display Name is also used in the Caption area if you insert the tool into a document.

5. If you want the new column to be automatically indexed, select the Index Column check box. You should only index columns when necessary. For example, when engineered columns have > 250 discrete values, indexing can start to improve query performance.

6. Optional setting: if you want to make the column permanent so that it can be viewed and selected in the Data Explorer, click the Optional expand icon. In the Table Column Name field, enter the name you want to use to display the column in the Data Explorer tab.
7. Populate the remaining Settings fields:
   • Field to Copy: The field that you wish to copy to the Primary table. It would generally be a unique field like a customer ID or transaction number.
   • Copy to Table: Select the table that you wish to create the new column on using the generic selector.
   • Filter Segment: Create a filter by dragging and dropping from the Data Explorer or use a tool from the menu to engineer a filter. In our example we are simply dragging a column created by the Rank by X tool and specifying that we want Rank = 0, that is the most profitable person in each household. Note that if no filter is used Engine selects the first record in the index, which may not necessarily be the first record in the table, to this selection. Therefore from a data perspective if no filter is used then the results should be considered as random.

   

   For example:

   

8. Click the Process Document button to create the new column on the primary table. In our example it will create a column on the household table that includes the Cust ID of the household resident that has the highest profitability score. Households that do not contain any customers will have null values for this column.

• Data Engineering
  • Data Engineering Tools Overview
  • Aggregate Functions
  • Aggregate
  • Auto Numeric Band
  • Copy Values To
  • Days Between
  • Decode
  • Engine Cleanup
  • Expression
  • Engine Expressions
  • First or Last
  • Make Permanent
  • Numeric Band
  • Parametric Groups
  • Percentile
  • Prioritize List
  • Quantile Band
  • Rank By X